package db

import (
	"context"
	"database/sql"
	"embed"
	"errors"
	"fmt"
	"github.com/golang-migrate/migrate/v4"
	"github.com/golang-migrate/migrate/v4/database/sqlite"
	"github.com/golang-migrate/migrate/v4/source/iofs"
	"github.com/xiyoufang/cloud-syncer/internal/http/model"
	"github.com/xiyoufang/cloud-syncer/internal/log"
	_ "modernc.org/sqlite"
	"strings"
	"time"
)

//go:embed migrations/sqlite/*.sql
var sqliteMigrationsFS embed.FS

var queries = []*Query{
	{
		Name: "添加新文件和有更新的文件到记录表",
		Sql: `insert into histories (name, size, modified, local_path, remote_path, status, created_at)
					select t.name                     as name,
						   t.size                     as size,
						   t.modified                 as modified,
						   t.local_path               as local_path,
						   t.remote_path              as remote_path,
						   0                          as status,
						   unixepoch('subsec') * 1000 as created_at
					from temp t
							 left join files f on (t.local_path = f.local_path)
					where f.id is null
					   or t.modified != f.modified
					   or t.size != f.size
					`,
	},
	{
		Name: "添加新文件到文件表",
		Sql: `insert into files (name, size, modified, local_path, remote_path, created_at, updated_at)
					select t.name                     as name,
						   t.size                     as size,
						   t.modified                 as modified,
						   t.local_path               as local_path,
						   t.remote_path              as remote_path,
						   unixepoch('subsec') * 1000 as created_at,
						   unixepoch('subsec') * 1000 as updated_at
					from temp t
							 left join files f on (t.local_path = f.local_path)
					where f.id is null`,
	},
	{
		Name: "更新文件信息，如果文件有变化",
		Sql: `update files
			set modified   = t.modified,
				size       = t.size,
				updated_at = unixepoch('subsec') * 1000
			from temp t
			where files.local_path = t.local_path
			  and (files.modified != t.modified or files.size != t.size)`,
	},
}

type SqliteRepository struct {
	dataSourceName string
	logger         *log.Logger
	db             *sql.DB
}

func NewSqliteRepository(dataSourceName string, logger *log.Logger) (Repository, error) {
	db, err := sql.Open(SQLITE, dataSourceName)
	if err != nil {
		return nil, err
	}
	// https://github.com/mattn/go-Sqlite?tab=readme-ov-file#faq
	db.SetMaxOpenConns(1)
	return &SqliteRepository{
		dataSourceName: dataSourceName,
		logger:         logger,
		db:             db,
	}, nil
}

func (repo *SqliteRepository) CommitOrRollback(tx *sql.Tx, err error) error {
	if err != nil {
		return tx.Rollback()
	} else {
		return tx.Commit()
	}
}

func (repo *SqliteRepository) Migrate() error {
	d, err := iofs.New(sqliteMigrationsFS, "migrations/sqlite")
	if err != nil {
		return err
	}
	dbInstance, err := sqlite.WithInstance(repo.db, &sqlite.Config{}) // Initialize migration database driver
	if err != nil {
		return err
	}
	m, err := migrate.NewWithInstance("iofs", d, SQLITE, dbInstance)
	if err != nil {
		return err
	}
	if err = m.Up(); err != nil && !errors.Is(err, migrate.ErrNoChange) {
		return err
	}
	return nil
}

func (repo *SqliteRepository) Vacuum() error {
	conn, err := repo.db.Conn(context.Background())
	if err != nil {
		return err
	}
	defer conn.Close()
	if _, err = conn.ExecContext(context.Background(), "vacuum"); err != nil {
		return err
	}
	return nil
}

func (repo *SqliteRepository) TruncateTempTable() (int64, error) {
	conn, err := repo.db.Conn(context.Background())
	if err != nil {
		return -1, err
	}
	defer conn.Close()
	tx, err := conn.BeginTx(context.Background(), &sql.TxOptions{})
	if err != nil {
		return -1, err
	}
	defer func() {
		_ = repo.CommitOrRollback(tx, err)
	}()

	result, err := tx.ExecContext(context.Background(), "delete from temp")
	if err != nil {
		return -1, err
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return -1, err
	}
	if _, err = tx.ExecContext(context.Background(), "delete from sqlite_sequence where name='temp'"); err != nil {
		return -1, err
	}
	return rowsAffected, nil
}

func (repo *SqliteRepository) InsertBatchFiles(files []File) (int64, error) {
	conn, err := repo.db.Conn(context.Background())
	if err != nil {
		return -1, err
	}
	defer conn.Close()
	tx, err := conn.BeginTx(context.Background(), nil)
	if err != nil {
		return -1, err
	}
	stmt, err := tx.Prepare(`insert into temp (name, size, modified, local_path, remote_path) values (?, ?, ?, ?, ?)`)
	if err != nil { // 创建一个预编译的sql失败
		return -1, err
	}
	defer func() {
		_ = stmt.Close()
		_ = repo.CommitOrRollback(tx, err)
	}()
	for _, file := range files {
		_, err = stmt.Exec(file.Name, file.Size, file.Modified, file.LocalPath, file.RemotePath)
	}
	if err != nil {
		return -1, err
	}
	return int64(len(files)), nil
}

func (repo *SqliteRepository) MergeFilesAndInsertHistories() error {
	conn, err := repo.db.Conn(context.Background())
	if err != nil {
		return err
	}
	defer conn.Close()
	tx, err := conn.BeginTx(context.Background(), nil)
	if err != nil {
		return err
	}
	defer func() {
		_ = repo.CommitOrRollback(tx, err)
	}()
	for _, query := range queries {
		var res sql.Result
		if res, err = tx.ExecContext(context.Background(), query.Sql); err != nil {
			return err
		}
		var rowsAffected int64
		if rowsAffected, err = res.RowsAffected(); err != nil {
			return err
		}
		repo.logger.Info("执行脚本: %s, 影响行数: %d", query.Name, rowsAffected)
	}
	return nil
}

func (repo *SqliteRepository) GetPendingHistories(limit int) ([]Task, error) {
	conn, err := repo.db.Conn(context.Background())
	if err != nil {
		return nil, err
	}
	defer conn.Close()
	rows, err := conn.QueryContext(context.Background(), "select id, local_path, remote_path from histories where status = 0 limit ?", limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var tasks []Task
	for rows.Next() {
		var task Task
		_ = rows.Scan(&task.Id, &task.LocalPath, &task.RemotePath)
		tasks = append(tasks, task)
	}
	return tasks, nil
}

func (repo *SqliteRepository) UpdateHistorySyncStatus(id int64, status int, message string) (int64, error) {
	conn, err := repo.db.Conn(context.Background())
	if err != nil {
		return -1, err
	}
	defer conn.Close()
	result, err := conn.ExecContext(context.Background(), "update histories set status = ?, message = ?, synced_at = ? where id = ?", status, message, time.Now().UnixMilli(), id)
	if err != nil {
		return -1, err
	}
	return result.RowsAffected()
}

func (repo *SqliteRepository) GetFilesPaginate(req model.PaginateReq) (model.PageVO, error) {
	conn, err := repo.db.Conn(context.Background())
	if err != nil {
		return model.PageVO{}, err
	}
	defer conn.Close()
	offset := (req.CurrentPage - 1) * req.PageSize
	whereSql, args := buildFileWhereSql(req.Search)
	var total int
	err = conn.QueryRowContext(context.Background(), fmt.Sprintf("select count(*) from files %s", whereSql), args...).Scan(&total)
	if err != nil {
		return model.PageVO{}, err
	}
	rows, err := conn.QueryContext(context.Background(), fmt.Sprintf("select id, name, size, modified, local_path, remote_path, created_at, updated_at from files %s order by id desc limit ? offset ?", whereSql), append(args, req.PageSize, offset)...)
	if err != nil {
		return model.PageVO{}, err
	}
	defer rows.Close()
	var records []model.FileVO
	for rows.Next() {
		var file model.FileVO
		err = rows.Scan(&file.Id, &file.Name, &file.Size, &file.Modified, &file.LocalPath, &file.RemotePath, &file.CreatedAt, &file.UpdatedAt)
		if err != nil {
			return model.PageVO{}, err
		}
		records = append(records, file)
	}
	return model.PageVO{Total: total, Records: records, Extra: nil}, nil
}

func (repo *SqliteRepository) GetHistoriesPaginate(req model.PaginateReq) (model.PageVO, error) {
	conn, err := repo.db.Conn(context.Background())
	if err != nil {
		return model.PageVO{}, err
	}
	defer conn.Close()
	offset := (req.CurrentPage - 1) * req.PageSize
	whereSql, args := buildHistoryWhereSql(req.Search)
	var total int
	err = conn.QueryRowContext(context.Background(), fmt.Sprintf("select count(*) from histories %s", whereSql), args...).Scan(&total)
	if err != nil {
		return model.PageVO{}, err
	}
	rows, err := conn.QueryContext(context.Background(), fmt.Sprintf("select id, name, size, modified, local_path, remote_path, status, message, synced_at, created_at from histories %s order by id desc limit ? offset ?", whereSql), append(args, req.PageSize, offset)...)
	if err != nil {
		return model.PageVO{}, err
	}
	defer rows.Close()
	var records []model.HistoryVO
	for rows.Next() {
		var history model.HistoryVO
		_ = rows.Scan(&history.Id, &history.Name, &history.Size, &history.Modified, &history.LocalPath, &history.RemotePath, &history.Status, &history.Message, &history.SyncedAt, &history.CreatedAt)
		records = append(records, history)
	}
	return model.PageVO{
		Total:   total,
		Records: records,
		Extra:   nil,
	}, nil
}

func (repo *SqliteRepository) GetSyncStats() (model.SyncStatsVO, error) {
	conn, err := repo.db.Conn(context.Background())
	if err != nil {
		return model.SyncStatsVO{}, err
	}
	defer conn.Close()
	var syncStats model.SyncStatsVO
	query := `select 
				(select count(*) from histories) as total_count,
				(select count(*) from histories where status = 2) as total_failed_count,
				(select count(*) from histories where synced_at >= (strftime('%s', 'now', '-24 hours') * 1000)) as last_24h_count,
				(select count(*) from histories where synced_at >= (strftime('%s', 'now', '-24 hours') * 1000) and status = 2) as last_24h_failed_count`
	err = conn.QueryRowContext(context.Background(), query).Scan(
		&syncStats.TotalCount,
		&syncStats.TotalFailedCount,
		&syncStats.Last24hCount,
		&syncStats.Last24hFailedCount)
	if err != nil {
		return syncStats, err
	}
	return syncStats, nil
}

func (repo *SqliteRepository) GetFileStats() (model.FileStatsVO, error) {
	conn, err := repo.db.Conn(context.Background())
	if err != nil {
		return model.FileStatsVO{}, err
	}
	defer conn.Close()
	var fileStats model.FileStatsVO
	query := `select count(*) from files`
	err = conn.QueryRowContext(context.Background(), query).Scan(&fileStats.TotalCount)
	if err != nil {
		return model.FileStatsVO{}, err
	}
	return fileStats, nil
}

func (repo *SqliteRepository) GetLast30dSyncStatistics() (model.Last30dSyncStatisticsVO, error) {
	conn, err := repo.db.Conn(context.Background())
	if err != nil {
		return model.Last30dSyncStatisticsVO{}, err
	}
	defer conn.Close()
	rows, err := conn.QueryContext(context.Background(), `
													select strftime('%Y-%m-%d', created_at / 1000, 'unixepoch')        as date,
														   count(*)                                                    as total,
														   count(iif(status = 0, 1, null))                             as pending,
														   count(iif(status = 1, 1, null))                             as success,
														   count(iif(status = 2, 1, null))                             as failed
													from histories
													where created_at >= (strftime('%s', 'now', '-30 days', 'start of day') * 1000)
													group by date
													order by date`)
	if err != nil {
		return model.Last30dSyncStatisticsVO{}, err
	}
	defer rows.Close()
	last30dSyncStatisticsVO := model.Last30dSyncStatisticsVO{
		Last30dSyncTotalCount:   make(map[string]int),
		Last30dSyncPendingCount: make(map[string]int),
		Last30dSyncSuccessCount: make(map[string]int),
		Last30dSyncFailedCount:  make(map[string]int),
	}
	for rows.Next() {
		var date string
		var total int
		var pending int
		var success int
		var failed int
		if err = rows.Scan(&date, &total, &pending, &success, &failed); err != nil {
			return model.Last30dSyncStatisticsVO{}, err
		} else {
			last30dSyncStatisticsVO.Last30dSyncTotalCount[date] = total
			last30dSyncStatisticsVO.Last30dSyncPendingCount[date] = pending
			last30dSyncStatisticsVO.Last30dSyncSuccessCount[date] = success
			last30dSyncStatisticsVO.Last30dSyncFailedCount[date] = failed
		}
	}
	return last30dSyncStatisticsVO, nil
}

func buildHistoryWhereSql(search map[string]interface{}) (string, []interface{}) {
	var conditions []string
	var args []interface{}
	if status, ok := search["status"]; ok {
		conditions = append(conditions, "status = ?")
		args = append(args, status)
	}
	if name, ok := search["name"]; ok {
		conditions = append(conditions, "name like ?")
		args = append(args, fmt.Sprint(name, "%"))
	}
	if localPath, ok := search["localPath"]; ok {
		conditions = append(conditions, "local_path like ?")
		args = append(args, fmt.Sprint(localPath, "%"))
	}
	if createdAt, ok := search["createdAt"]; ok {
		switch createdAt.(type) {
		case []interface{}:
			createdAtSlice := createdAt.([]interface{})
			if len(createdAtSlice) == 2 {
				conditions = append(conditions, "created_at between ? and ?")
				args = append(args, createdAtSlice...)
			}
		}
	}
	if len(conditions) > 0 {
		return " where " + strings.Join(conditions, " and "), args
	}
	return "", args
}

func buildFileWhereSql(search map[string]interface{}) (string, []interface{}) {
	var conditions []string
	var args []interface{}
	if name, ok := search["name"]; ok {
		conditions = append(conditions, "name like ?")
		args = append(args, fmt.Sprint(name, "%"))
	}
	if localPath, ok := search["localPath"]; ok {
		conditions = append(conditions, "local_path like ?")
		args = append(args, fmt.Sprint(localPath, "%"))
	}
	if modified, ok := search["modified"]; ok {
		switch modified.(type) {
		case []interface{}:
			modifiedSlice := modified.([]interface{})
			if len(modifiedSlice) == 2 {
				conditions = append(conditions, "modified between ? and ?")
				args = append(args, modifiedSlice...)
			}
		}
	}
	if len(conditions) > 0 {
		return " where " + strings.Join(conditions, " and "), args
	}
	return "", args
}
